
package cn.javaxc.mvcwork.modules.notifier;
import cn.javaxc.mvcwork.controller.web.PagedListInfo;
import cn.javaxc.mvcwork.util.common.DatabaseUtils;
import java.sql.*;
import java.util.*;




public class MessageAttachmentList extends ArrayList {
  private PagedListInfo pagedListInfo = null;
  public final static String tableName = "message_file_attachment";
  public final static String uniqueField = "attachment_id";
  private String linkModuleId = null;
  private int linkItemId = -1;

  public MessageAttachmentList() {
  }

  public MessageAttachmentList(Connection db, String linkModuleId, int linkItemId) throws SQLException {
    this.linkModuleId = linkModuleId;
    this.linkItemId = linkItemId;
    buildList(db);
  }

  
  public String getTableName() {
    return tableName;
  }


  
  public String getUniqueField() {
    return uniqueField;
  }

  
  public String getLinkModuleId() {
    return linkModuleId;
  }

  
  public void setLinkModuleId(String tmp) {
    this.linkModuleId = tmp;
  }


  
  public int getLinkItemId() {
    return linkItemId;
  }

  
  public void setLinkItemId(int tmp) {
    this.linkItemId = tmp;
  }

  
  public void setLinkItemId(String tmp) {
    this.setLinkItemId(Integer.parseInt(tmp));
  }

  
  public void addItem(int fileId) {
    MessageAttachment thisMessageAttachment = new MessageAttachment();
    thisMessageAttachment.setFileItemId(fileId);
    if (this.size() == 0) {
      this.add(thisMessageAttachment);
    } else {
      this.add(0, thisMessageAttachment);
    }
  }

  
  private void createFilter(Connection db, StringBuffer sqlFilter) {
    if (sqlFilter == null) {
      sqlFilter = new StringBuffer();
    }
    if (linkModuleId !=null&&!linkModuleId.isEmpty()) {
      sqlFilter.append("AND mfa.link_module_id = ? ");
    }
    if (linkItemId > -1) {
      sqlFilter.append("AND mfa.link_item_id = ? ");
    }
  }


  
  private int prepareFilter(PreparedStatement pst) throws SQLException {
    int i = 0;
    if (linkModuleId !=null&&!linkModuleId.isEmpty()) {
      pst.setString(++i, linkModuleId);
    }
    if (linkItemId > -1) {
      pst.setInt(++i, linkItemId);
    }
    return i;
  }

  
  public void buildList(Connection db) throws SQLException {
    PreparedStatement pst = null;
    ResultSet rs = null;
    int items = -1;
    StringBuffer sqlSelect = new StringBuffer();
    StringBuffer sqlCount = new StringBuffer();
    StringBuffer sqlFilter = new StringBuffer();
    StringBuffer sqlOrder = new StringBuffer();
    //Need to build a base SQL statement for counting records
    sqlCount.append(
        "SELECT COUNT(*) AS recordcount " +
            "FROM message_file_attachment mfa " +
            "WHERE mfa.attachment_id  > -1 ");
    createFilter(db, sqlFilter);
    if (pagedListInfo != null) {
      //Get the total number of records matching filter
      pst = db.prepareStatement(sqlCount.toString() + sqlFilter.toString());
      items = prepareFilter(pst);
      rs = pst.executeQuery();
      if (rs.next()) {
        int maxRecords = rs.getInt("recordcount");
        pagedListInfo.setMaxRecords(maxRecords);
      }
      rs.close();
      pst.close();
      //Determine the offset, based on the filter, for the first record to show
      if (!pagedListInfo.getCurrentLetter().equals("")) {
        pst = db.prepareStatement(
            sqlCount.toString() +
                sqlFilter.toString() +
                "AND " + DatabaseUtils.toLowerCase(db) + "(mfa.filename) < ? ");
        items = prepareFilter(pst);
        pst.setString(++items, pagedListInfo.getCurrentLetter().toLowerCase());
        rs = pst.executeQuery();
        if (rs.next()) {
          int offsetCount = rs.getInt("recordcount");
          pagedListInfo.setCurrentOffset(offsetCount);
        }
        rs.close();
        pst.close();
      }
      //Determine column to sort by
      pagedListInfo.setDefaultSort("mfa.filename", null);
      pagedListInfo.appendSqlTail(db, sqlOrder);
    } else {
      sqlOrder.append("ORDER BY mfa.filename ");
    }
    //Build a base SQL statement for returning records
    if (pagedListInfo != null) {
      pagedListInfo.appendSqlSelectHead(db, sqlSelect);
    } else {
      sqlSelect.append("SELECT ");
    }
    sqlSelect.append(
        "mfa.* " +
            "FROM message_file_attachment mfa " +
            "WHERE mfa.attachment_id  > -1 ");
    pst = db.prepareStatement(
        sqlSelect.toString() + sqlFilter.toString() + sqlOrder.toString());
    items = prepareFilter(pst);
    if (pagedListInfo != null) {
      pagedListInfo.doManualOffset(db, pst);
    }
    rs = pst.executeQuery();
    if (pagedListInfo != null) {
      pagedListInfo.doManualOffset(db, rs);
    }
    while (rs.next()) {
      MessageAttachment thisAttachment = new MessageAttachment(rs);
      thisAttachment.buildFileItems(db, false);
      this.add(thisAttachment);
    }
    rs.close();
    pst.close();
  }


}
